import os

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'crm_homework.settings')
import django

django.setup()

from app01 import models

# 1. 查找所有书名里包含金老板的书
ret = models.Book.objects.filter(title__contains='金老板')
for i in ret:
    print(i)

print("-" * 50)
# 2. 查找出版日期是2018年的书
ret = models.Book.objects.filter(publish_date__year='2018')
for j in ret:
    print(j)
print("-" * 50)
# 3. 查找出版日期是2017年的书名
ret = models.Book.objects.filter(publish_date__year='2017').values('title')
print('3----->', ret)
print("-" * 50)
# 4. 查找价格大于10元的书
ret = models.Book.objects.filter(price__gt=10).values('price')
print('4----->', ret)
print("-" * 50)
# 5. 查找价格大于10元的书名和价格
ret = models.Book.objects.filter(price__gt=10).values('title', 'price')
print('5----->', ret)
print("-" * 50)
# 6. 查找memo字段是空的书
from django.db.models import Q

# ret = models.Book.objects.filter(memo__isnull=True)
ret = models.Book.objects.filter(Q(memo__isnull=True) | Q(memo=''))
print('6----->', ret)
print("-" * 50)
# 7. 查找在北京的出版社
ret = models.Publisher.objects.filter(city='北京')
print('7----->', ret)
print("-" * 50)
# 8. 查找名字以沙河开头的出版社
ret = models.Publisher.objects.filter(name__startswith='沙河')
print('8----->', ret)
print("-" * 50)
# 9. 查找“沙河出版社”出版的所有书籍
ret = models.Book.objects.filter(publisher__name='沙河出版社')
ret2 = models.Publisher.objects.filter(name='沙河出版社').values('book__title')
print('9----->', ret2)
print('9----->', ret)

print('==================================聚合函数==================================')

from django.db.models import Max, Count

# 10. 查找每个出版社出版价格最高的书籍价格
ret = models.Publisher.objects.annotate(Max('book__price')).values()
for f in ret:
    print(f)
print("-" * 50)
# 11. 查找每个出版社的名字以及出的书籍数量
ret = models.Publisher.objects.annotate(count=Count('book')).values('name', 'count')
for m in ret:
    print(m)
print("-" * 50)
# 12. 查找作者名字里面带“小”字的作者
ret = models.Author.objects.filter(name__contains='小')
print('12----->', ret)
print("-" * 50)
# 13. 查找年龄大于30岁的作者
ret = models.Author.objects.filter(age__gt=30)
print('13----->', ret)
print("-" * 50)
# 14. 查找手机号是155开头的作者
ret = models.Author.objects.filter(phone__startswith='155')
print('14----->', ret)
print("-" * 50)
# 15. 查找手机号是155开头的作者的姓名和年龄
ret = models.Author.objects.filter(phone__startswith='155').values('name', 'age')
print('15----->', ret)
print("-" * 50)
# 16. 查找每个作者写的价格最高的书籍价格
ret = models.Author.objects.annotate(max=Max('book__price')).values('name', 'max')
print('16----->', ret)
print("-" * 50)
# 17. 查找每个作者的姓名以及出的书籍数量
ret = models.Author.objects.annotate(count=Count('book')).values('name', 'count')
print('17----->', ret)
print("-" * 50)
# 18. 查找书名是“跟金老板学开车”的书的出版社
ret = models.Publisher.objects.filter(book__title='跟金老板学开车')
print('18----->', ret)
print("-" * 50)
# 19. 查找书名是“跟金老板学开车”的书的出版社所在的城市
ret = models.Publisher.objects.filter(book__title='跟金老板学开车').values('city')
print('19----->', ret)
print("-" * 50)
# 20. 查找书名是“跟金老板学开车”的书的出版社的名称
ret = models.Publisher.objects.filter(book__title='跟金老板学开车').values('name')
print('20----->', ret)
print("-" * 50)
# 21. 查找书名是“跟金老板学开车”的书的出版社出版的其他书籍的名字和价格
ret = models.Book.objects.filter(publisher=models.Publisher.objects.filter(book__title='跟金老板学开车').first()).exclude(
    title='跟金老板学开车')
print('21----->', ret)
print("-" * 50)
# 22. 查找书名是“跟金老板学开车”的书的所有作者
ret = models.Publisher.objects.filter(book__title='跟金老板学开车').values('book__author__name')
print('22----->', ret)
print("-" * 50)
# 23. 查找书名是“跟金老板学开车”的书的作者的年龄
ret = models.Publisher.objects.filter(book__title='跟金老板学开车').values('book__author__age')
print("23----->", ret)
print("-" * 50)
# 24. 查找书名是“跟金老板学开车”的书的作者的手机号码
ret = models.Publisher.objects.filter(book__title='跟金老板学开车').values('book__author__phone')
print("24----->", ret)
print("-" * 50)
# 25. 查找书名是“跟金老板学开车”的书的作者们的姓名以及出版的所有书籍名称和价钱
ret = models.Author.objects.values('name', 'book__title', "book__price").filter(book__title='跟金老板学开车').distinct()
for n in ret:
    print("25----->", n)

# 事务
from django.db import transaction

try:
    with transaction.atomic():
        models.Author.objects.create(name='egon', age=18)
        models.Author.objects.create(name='egon', age='xxxxx', phone='15231254784')
        models.Author.objects.create(name='egon', age=18)
        models.Author.objects.create(name='egon', age=18)
except Exception as e:
    print(str(e))
